-- ==============================================================================================

CREATE OR REPLACE PROCEDURE ZOEDEVOPS.ZOEPRC_EXEC_SQL(iv_json_sql IN OUT varchar2)
AUTHID CURRENT_USER	
-- Created in 2019.06.25 by polarbears
-- Copyright (c) 20xx, CHINA and/or affiliates.
-- All rights reserved.
--	Name:
-- 		zoeprc_exec_sql.sql
--	Description:
-- 		
--  Relation:
--      zoeUtility
--	Notes:
--		
--	修改 - （年-月-日） - 描述
--  

 AS
PRAGMA AUTONOMOUS_TRANSACTION;
lv_sql          VARCHAR2(4000);
lv_sql_type     VARCHAR2(64);
lv_sql_return   VARCHAR2(4000);
lv_error_return VARCHAR2(4000);
lv_error_type   ZOEDEVOPS.DVP_ERROR_LOG.ERROR_TYPE%TYPE;
lv_error_info   ZOEDEVOPS.DVP_ERROR_LOG.ERROR_INFO%TYPE;
BEGIN
    --  设置程序名上下文
    ZOEDEVOPS.ZOEPRC_SET_DEVOPS_CONTEXT('CURRENT_PROGRAM','ZOEDEVOPS.ZOEPRC_EXEC_SQL');
    SELECT JSON_VALUE(iv_json_sql,'$.SQL_TYPE'),
        JSON_VALUE(iv_json_sql,'$.SQL_TEXT')
    INTO lv_sql_type,lv_sql
    FROM DUAL;
    CASE lv_sql_type
        WHEN 'DQL' THEN
            EXECUTE IMMEDIATE lv_sql;
            iv_json_sql := '{"SQL_COUNT":"'||SQL%ROWCOUNT||'"}';
        WHEN 'DML' THEN
            EXECUTE IMMEDIATE lv_sql;
            iv_json_sql := '{"SQL_COUNT":"'||SQL%ROWCOUNT||'"}';
        WHEN 'DDL' THEN
            --dbms_output.put_line(lv_sql);
            EXECUTE IMMEDIATE lv_sql;
        WHEN 'DQLRESULT' THEN
            EXECUTE IMMEDIATE lv_sql into iv_json_sql;
        ELSE NULL;
    END CASE;
EXCEPTION
    WHEN OTHERS THEN
        lv_error_type := 'PLSQL';
        lv_error_info := '{"ERROR_TYPE":"'||lv_error_type||'",';
        lv_error_info := lv_error_info || '"ERROR_CONTENT":';
        lv_error_info := lv_error_info || '{"ERROR_PROGRAM":"'||SYS_CONTEXT('ZOE_DEVOPS_CONTEXT','CURRENT_PROGRAM')||'",';
        lv_error_info := lv_error_info || '"ERROR_MESSAGE":"'||SQLERRM||'",';
        lv_error_info := lv_error_info || '"ERROR_SQL":"'||SUBSTR(lv_sql,1,2000)||'"}}';
        lv_error_return := ZOEDEVOPS.ZOEFUN_WERROR_LOG(lv_error_info);
    
END ZOEPRC_EXEC_SQL;
/
